using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using Commons.Models.Users;
using Commons.Preferences;

namespace Commons.Daos.Users
{
	public class UserDao : SQLDao<Dictionary<long, User>>, IUserDao
	{
        private const string CreateSql = "create table user (name text primary key, id integer primary key, pass text)";
        private const string SelectAllSql = "select name, id, pass from user";
        private const string SelectMax = "select max(id) from user";
        private const string InsertSql = "insert into user (name, id, pass) OUTPUT INSERTED.ID values (?, ?, ?)";
		private const string UpdateSql = "update user set name = ?, pass = ? where id = ?";
        private const string DeleteSql = "delete from user where id = ?";

		protected override IEnumerable<string> GetUpgradeSqls(int oldVersion)
		{
			if (oldVersion == 0)
			{
				yield return CreateSql;
			}
		}

        public long GetLastIndex ()
        {
            return DaoSupport.QueryForLong(SelectMax);
        }

        public IEnumerable<User> GetAll()
        {
            if (cache == null)
            {
                cache = DaoSupport.QueryForDictionary<long, User>(new UserRowMapper(), 0, SelectAllSql);
            }

            return cache.Values;
        }
		
        public User Get(long userId)
		{
            GetAll();

            User user;

            return cache.TryGetValue(userId, out user) ? user : null;
		}

		public void Insert(User user)
		{
            SqlParameter name = new SqlParameter("name", user.Name);
            SqlParameter userId = new SqlParameter("id", user.Id);
            SqlParameter pass = new SqlParameter("pass", user.Password);
			DaoSupport.Insert(InsertSql, name,  userId, pass);

            if (cache != null)
                cache.Add(user.Id, user);
		}

		public void Update(User user)
		{
            SqlParameter name = new SqlParameter("name", user.Name);
            SqlParameter pass = new SqlParameter("pass", user.Password);
            SqlParameter userId = new SqlParameter("userId", user.Id);
			DaoSupport.Update(UpdateSql, name, pass, userId);
            
            if (cache != null)
                cache[user.Id] = user;
		}

		public void DeleteUser (User user)
		{
            SqlParameter userId = new SqlParameter("userId", user.Id);
            DaoSupport.Delete(DeleteSql, userId);
		}

		protected class UserRowMapper : IRowMapper<User>
		{
			public User MapRow(DbDataReader reader)
			{
                string name = reader.GetString(0);
                long id = reader.GetInt64(1);
                string pass = reader.GetString(2);
				
				return new User(id, name, pass);
			}
		}
	}
}